using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using PHSRAG.Insight21.BusinessObjects;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using PHSRAG.Utility;

namespace PHSRAG.Insight21.ClinicalTrials
{
   /// <summary>
   /// The BudgetExpenses class represents a collection of BudgetExpense objects.  It is 
   /// responsible for loading and saving the Budget Expenses
   /// </summary>
   public class BudgetExpenses : SaveableBusinessObject, IEnumerable
   {

      #region Constants
      private const string BudgetExpensesKey = "BudgetExpenses_0A7C68A2-2BFA-4e01-9207-141743F8225A";
      private const string TableName = "BudgetExpenses";
      #endregion

      #region Instance Variables
      private ArrayList budgetExpenses = new ArrayList( );
      private Budget budget;
      private SqlCommand updateBudgetExpensesCommand;
      private SqlCommand deleteBudgetExpensesCommand;
      #endregion

      #region Constructor
      /// <summary>
      /// Initialize the base class with the specified cache policy and create the data container.
      /// </summary>
      public BudgetExpenses( ICachePolicy cachePolicy ) : base ( cachePolicy )
      {
         DataContainer = new DataSet( );
         budget = (Budget)CachePolicy.GetCachedObject( Budget.CacheKey );
      }
      #endregion
   
      #region Base Methods
      /// <summary>
      /// Cache this instance using the prevalent cache policy.
      /// </summary>
      public override void Cache( )
      {
         CachePolicy.Cache( BudgetExpensesKey, this );
      }

      /// <summary>
      /// Remove this instance from the cache using the prevalent cache policy.
      /// </summary>
      public override void UnCache( )
      {
         CachePolicy.UnCache( BudgetExpensesKey );
      }

      /// <summary>
      /// Load expense details (variable costs or not) pertaining to a specific budget.
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Object array which must contain the budget ID, and a boolean indicating
      /// whether to get variable costs or not.</param>
      public override void Load( string connectionString, params object[] args )
      {
         if ( args.Length != 2 )
            throw new Exception("BudgetExpenses load requires a budget id and variable cost indicator.");
			
         DataAccess.LoadDataSet( DataContainer, TableName, connectionString, "GetClinicalTrialBudgetExpenses", 
            Utility.DataAccess.BuildSqlParameterArray( "@budgetId", SqlDbType.Int, args[0],
                                                       "@variableCosts", SqlDbType.Bit, args[1] ) );

         if ( DataContainer.Tables.Count == 0 )
            throw new Exception("Failed to load Budget Expense information.");

         foreach( DataRow budgetExpenseRow in DataContainer.Tables[TableName].Rows )
         {
            budgetExpenses.Add( new BudgetExpense( CachePolicy, budgetExpenseRow ) );
         }
      }

      /// <summary>
      /// Saves expense details associated with this budget.  
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Optional parameters (ignored)</param>
      public override void Save( string connectionString, params object[] args )
      {
         SqlConnection connection = new SqlConnection( connectionString );
         SqlTransaction transaction = null;
         try
         {
            connection.Open( );
            transaction = connection.BeginTransaction( );
            BuildCommands( );
            updateBudgetExpensesCommand.Connection = connection;
            updateBudgetExpensesCommand.Transaction = transaction;
            deleteBudgetExpensesCommand.Connection = connection;
            deleteBudgetExpensesCommand.Transaction = transaction;

            SqlDataAdapter adapter = new SqlDataAdapter( );
            adapter.InsertCommand = updateBudgetExpensesCommand;
            adapter.UpdateCommand = updateBudgetExpensesCommand;
            adapter.DeleteCommand = deleteBudgetExpensesCommand;
            adapter.Update( DataContainer, TableName );

            transaction.Commit( );
         }
         catch( Exception e )
         {
            transaction.Rollback( );
            throw(new Exception(@"Error saving Budget Expenses to the database.", e));
         }
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }
      #endregion

      #region Private Methods
      private void BuildCommands( )
      {
         if ( updateBudgetExpensesCommand == null )
         {
            updateBudgetExpensesCommand = new SqlCommand( );
            updateBudgetExpensesCommand.CommandText = "UpdateClinicalTrialBudgetExpenses";
            updateBudgetExpensesCommand.CommandType = CommandType.StoredProcedure;
			updateBudgetExpensesCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramId = new SqlParameter( "@budgetExpenseId", SqlDbType.Int );
            paramId.SourceColumn = "Id";
            paramId.Direction = ParameterDirection.InputOutput;
            updateBudgetExpensesCommand.Parameters.Add( paramId );
            updateBudgetExpensesCommand.Parameters.Add( "@budgetId", SqlDbType.Int ).SourceColumn = "ClinicalTrialBudgetId";
            updateBudgetExpensesCommand.Parameters.Add( "@studyCostCode", SqlDbType.VarChar ).SourceColumn = "StudyCostCode";
            updateBudgetExpensesCommand.Parameters.Add( "@otherExpenseText", SqlDbType.VarChar ).SourceColumn = "OtherExpenseText";
            updateBudgetExpensesCommand.Parameters.Add( "@expenseCost", SqlDbType.Decimal ).SourceColumn = "ExpenseCost";
            updateBudgetExpensesCommand.Parameters.Add( "@applyOverhead", SqlDbType.Bit ).SourceColumn = "ApplyOverhead";
            updateBudgetExpensesCommand.Parameters.Add( "@variableCost", SqlDbType.Bit ).SourceColumn = "VariableCost";
         }

         if ( deleteBudgetExpensesCommand == null )
         {
            deleteBudgetExpensesCommand = new SqlCommand( );
            deleteBudgetExpensesCommand.CommandText = "DeleteClinicalTrialBudgetExpenses";
            deleteBudgetExpensesCommand.CommandType = CommandType.StoredProcedure;
			deleteBudgetExpensesCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramDeleteId = new SqlParameter( "@budgetExpenseId", SqlDbType.Int );
            paramDeleteId.SourceColumn = "Id";
            paramDeleteId.Direction = ParameterDirection.Input;
            deleteBudgetExpensesCommand.Parameters.Add( paramDeleteId );
         }
      }
      #endregion

      #region IEnumerable Methods
      /// <summary>
      /// Gets an IEnumerator interface.  Required for implementation of IEnumerable
      /// </summary>
      /// <returns>IEnumerator</returns>
      public IEnumerator GetEnumerator( )
      {
		   return budgetExpenses.GetEnumerator( );
      }
      #endregion

      #region Public Collection helpers
      /// <summary>
      /// The Count property returns the number of items in the Budget Expenses collection
      /// </summary>
      public int Count
      {
         get
         {
            return budgetExpenses.Count;
         }
      }

      /// <summary>
      /// Indexer to get a specific Budget Expense from the collection
      /// </summary>
      public BudgetExpense this[int index]
      {
         get
         {
            return (BudgetExpense)budgetExpenses[ index ];
         }
      }
      #endregion

      #region Public Methods
      /// <summary>
      /// Creates a new BudgetExpense and adds the item to the collection.  Also sets default
      /// properties on the BudgetExpense.
      /// </summary>
      /// <param name="varCost">Boolean indicating whether this is a new variable cost or not</param>
      /// <returns>BudgetExpense</returns>
      public BudgetExpense CreateNewBudgetExpense( bool varCost )
      {
         DataRow newRow = DataContainer.Tables[TableName].NewRow( );
         DataContainer.Tables[TableName].Rows.Add( newRow );
         BudgetExpense newBudgetExpense = new BudgetExpense( CachePolicy, newRow );
         newBudgetExpense.BudgetId = budget.Id;
         newBudgetExpense.VariableCost = varCost;
         newBudgetExpense.ApplyOverhead = true;
         if ( varCost )
            newBudgetExpense.ExpenseCost = "0";
         budgetExpenses.Add( newBudgetExpense );
		   return newBudgetExpense;
      }

      /// <summary>
      /// Removes a specific BudgetExpense from the collection.  Must call save on BudgetExpenses
      /// to save this change to the database.
      /// </summary>
      /// <param name="budgetExpense">BudgetExpense to remove from the collection.</param>
      public void RemoveBudgetExpense( BudgetExpense budgetExpense )
      {
         DataContainer.Tables[TableName].Select( string.Format( "Id = {0}", budgetExpense.Id ) )[0].Delete( );
         budgetExpenses.Remove( budgetExpense );
      }
      #endregion

   }// END CLASS DEFINITION BudgetExpenses
}